package db;
import model.*;

import java.sql.*;
import java.util.ArrayList;

/**
 * DBWorkOut.java
 *
 */
public class DBWorkOut {
	private  Connection con;
	/** Creates a new instance 
	 * of DBWorkOut */
	public DBWorkOut() {
		con = DBConnection.getInstance().getDBcon();
	}
	//Implements the methods from the interface
	// get all WorkOuts
	public ArrayList<WorkOut> getAllWorkOut(boolean retriveAssociation)
	{
		return miscWhere("", retriveAssociation);
	}
	/*  //find WorkOut of type and distance:
    public WorkOut findWorkOut(String type, int distance,  boolean retriveAssociation)
    {   String wClause = "  type = '" + type + "AND distance = '" + distance + "'";
        return singleWhere(wClause, retriveAssociation);
    }
	 */

	//find one WorkOut having the id
	public WorkOut searchWorkOut(int id, boolean retriveAssociation)
	{
		String wClause = "id = '" + id + "'";
		System.out.println("WorkOut " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}

	//insert a WorkOut
	public int insertWorkOut(WorkOut workOut,String userId, int exerciseId)
	{     
		int rc = -1;
		String query="INSERT INTO WorkOut(date, energy, note, time, isCompleted, userId, exerciseId)  " +
		"VALUES('"+
		workOut.getDate()			+ "','"  +
		workOut.getEnergy()			+ "','"  +
		workOut.getNote()			+ "','"  +
		workOut.getTime()			+ "','"	 +
		workOut.getIsCompleted()	+ "','"  +  
		userId						+ "','"  + 
		exerciseId					+ "')"; 

		System.out.println("insert : " + query);		  					
		try{ // insert new WorkOut
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Insert exception in WorkOut db: "+ex);
		}
		return(rc);
	}

	public int updateWorkOut(WorkOut workOut)
	{
		WorkOut workOutObj  = workOut;
		int rc=-1;

		String query="UPDATE WorkOut SET "+

		"date ='" + workOutObj.getDate() 		+"' "+
		"energy ='" + workOutObj.getEnergy() 	+"' "+
		"note ='" + workOutObj.getNote() 		+"' "+
		"time ='" + workOutObj.getTime() 		+"' "+
		"isCompleted ='" + workOutObj.getIsCompleted()	+"' "+
		"energy" + workOutObj.getEnergy() 		+"' "+

		" WHERE id = '" + workOutObj.getId() + "'";
		System.out.println("Update query:" + query);
		try{ // update WorkOut
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//slut try	
		catch(Exception ex){
			System.out.println("Update exception in WorkOut db: "+ex);
		}
		return(rc);
	}

	public int delete(String id)
	{
		int rc=-1;

		String query="DELETE FROM WorkOut WHERE id = '" +
		id + "'";
		System.out.println(query);
		try{ // delete from WorkOut
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//slut try	
		catch(Exception ex){
			System.out.println("Delete exception in WorkOut db: "+ex);
		}
		return(rc);
	}

	public ResultSet getUserWorkOuts(String userId){
		ResultSet results = null;

		String query ="SELECT WorkOut.date, SportsExercise.type, SportsExercise.distance, WorkOut.time " +
		"FROM WorkOut INNER JOIN SportsExercise ON WorkOut.exerciseId=SportsExercise.id "+
		"WHERE WorkOut.userId='" +userId+ "'"+
		"ORDER BY WorkOut.date";

		try{ // read the WorkOut from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);



		}
		catch(Exception e) {
			System.out.println("Exception in tableResults: " +e);

		}


		return results;

	}



	//private methods
	//michWere is used whenever we want to select more than one WorkOut


	private ArrayList<WorkOut> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<WorkOut> list = new ArrayList<WorkOut>();	

		String query =  buildQuery(wClause);

		try{ // read the WorkOut from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			//int snr=0;
			while( results.next() ){
				WorkOut workOutObj = new WorkOut();
				workOutObj = buildWorkOut(results);	
				list.add(workOutObj);	
			}//end while
			// if retrieveAssociation is true, the supervisor and department
			// has to be selected for each object in the list
			stmt.close();
		}//slut try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}



	//Singelwhere is used when we only select one WorkOut 	
	private WorkOut singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		WorkOut workOutObj = new WorkOut();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the WorkOut from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				workOutObj = buildWorkOut(results);                            
			}

		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return workOutObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query="SELECT * FROM WorkOut";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	//method to build an WorkOut object
	private WorkOut buildWorkOut(ResultSet results){   
		WorkOut workOutObj = new WorkOut();
		try{
			workOutObj.setId(results.getInt(0));
			workOutObj.setDate(results.getString(1));
			workOutObj.setEnergy(results.getDouble(2));
			workOutObj.setNote(results.getString(3));
			workOutObj.setTime(results.getString(4));
			workOutObj.setIsCompleted(results.getBoolean(5)); 

		}
		catch(Exception e)
		{
			System.out.println("error in building the WorkOut object");
		}
		return workOutObj;
	}



}  


